package com.example.qa_back.utils;

import com.example.qa_back.entity.QA;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * @author 董帅
 * @date 2021/3/6 - 20:40
 */

/*
* 功能：处理导入的Excel文件
* */
public class HandleExcelImport {
    public static Object getCellFormatValue(Cell cell) {
        Object cellValue = "";
        if (cell != null) {
            switch (cell.getCellTypeEnum()) {
                case NUMERIC:
                    Double d = cell.getNumericCellValue();
                    DecimalFormat df = new DecimalFormat("#.##");
                    cellValue = df.format(d);
                    break;
                case STRING:
                    cellValue = cell.getRichStringCellValue().getString();
                    break;
                default:
                    cellValue = "";
            }
        }
        return cellValue;
    }
    public static List<QA> getQAs(MultipartFile file) {
        List<QA> qaList = new ArrayList<>();
        Workbook wb = null;
        Sheet sheet;
        Row row;
        String name = file.getOriginalFilename();
        assert name != null;
        String ext = name.substring(name.lastIndexOf("."));
        try {
            InputStream is = file.getInputStream();
            if (".xls".equals(ext)) {
                wb = new HSSFWorkbook(is);
            } else if (".xlsx".equals(ext)) {
                wb = new XSSFWorkbook(is);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        if (wb != null) {
            sheet = wb.getSheetAt(0);
            int rowNum = sheet.getLastRowNum() + 1;
            row = sheet.getRow(0);
            int colNum = row.getPhysicalNumberOfCells();
            for (int i = 1; i < rowNum; ++i) {
                QA qa = new QA();
                row = sheet.getRow(i);
                for (int j = 0; j < colNum; ++j) {
                    if (j == 0) {
                        qa.setQuestion(HandleExcelImport.getCellFormatValue(row.getCell(j)).toString());
                    } else if (j == 1) {
                        qa.setAnswer(HandleExcelImport.getCellFormatValue(row.getCell(j)).toString());
                    }
                }
                qaList.add(qa);
            }
        }
        return qaList;
    }
}
